/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Dolita, 2007-12-24
 * @Version: 071224.001
 * Data access object of Marker model
 */
package com.ibsol.database
{
	import com.ibsol.model.Marker;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class MarkerDAO
	{
		private var _tourixDBConn:SQLConnection;
		/**
		 * Default constructor of Marker DAO, get database connection
		 */
		public function MarkerDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/**
		 * Insert data into marker table
		 * marker(id, cityMapid, packageid, cityid, x, my, title, desp, offlineOperator, updateTime)
		 * @param: marker:Marker			marker item object which will be populating marker table
		 * 		   resultHandler:Function	result function adapter
		 * 		   faultHandler:Function	fault function adapter
		 * @return: void
		 */
		public function insertMarker(marker:Marker, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO marker (cityMapid, packageid, cityid, x, y, title, desp, offlineOperator, updateTime, sequence) " + 
						"VALUES (" + marker.cityMapid + "," + marker.packageid + ","+ marker.cityid + "," + marker.mx + 
						", " + marker.my + ", '" + marker.mtitle + "', '" + marker.desp + "', 'NEW', 'T'," + marker.sequence + ")";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}							
		/**
		 * Load marker according to packageid and cityMapid
		 * @param:	packageid:int			package id which the desired marker belongs to
		 * 			cityMapid:int			city Map id which the desired marker belongs to
		 * 			resultHandler:Function	result function adapter, return marker array collection
		 * 			faultHandler:Function	fault function adapter
		 * @return: void
		 */
		public function loadMarkersByPackageMap(packageid:int, cityMapid:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM marker WHERE packageid = " + packageid + " AND " +
					"cityMapid = " + cityMapid + " AND offlineOperator != 'DEL' ORDER BY sequence asc";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a marker record in database. For different level of user, there are different options.
		 * marker(id, cityMapid, packageid, cityid, x, my, title, desp, offlineOperator, updateTime)
		 * @param:	marker:Marker			marker object which will be updated
		 * 			resultHandler:Function	result handler function
		 * 			faultHandler:Function	fault handler function
		 * @return: void
		 */	
		public function updateMarker(marker:Marker, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE marker SET cityMapid=" + marker.cityMapid + ", packageid=" + marker.packageid + 
					", cityid= " + marker.cityid + ", x = " + marker.mx + ", y = " + marker.my + ", title='" + marker.mtitle + 
					"', desp='" + marker.desp + "',offlineOperator = 'EDIT',updateTime='T', sequence=" +
					marker.sequence + " WHERE id = " + marker.id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		
		public function removeMarker(marker:Marker, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE marker SET offlineOperator = 'DEL', updateTime='T' WHERE id = " + marker.id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Permanently delete marker from database, whose offlineOperator is set to "DEL". If user wants to delete city, 
		 * user just updates offlineOperator to "DEL".
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return:	void
		 */
		public function clearDeletedMarkers(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "DELETE FROM marker WHERE offlineOperator = 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Sets offline operator to "", this function called after synchronization with server
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return: void
		 */
		public function resetOfflineOperator(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE marker SET offlineOperator=''";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Converts database object to Marker class
		 * @param:	o:Object	database return object
		 * @return:	Marker		Marker object after conversion
		 */
		private function typeObject(o:Object):Marker
		{ 
			var m:Marker = new Marker();
			m.id = o.id;
			m.cityMapid = o.cityMapid;
			m.packageid = o.packageid;
			m.cityid = o.cityid;
			m.mx = o.x;
			m.my = o.y;
			m.mtitle = o.title;
			m.desp = o.desp;
			m.offlineOperator = o.offlineOperator;
			m.updateTime = o.updateTime;
			m.sequence = o.sequence;
			
			return m;
		}
		/**
		 * Converts array object to Marker array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection of Marker after conversion from Array
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}
	}
}